// Copyright (c) 2002 HTHK
package com.ffpc.sql;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import com.ffpc.util.DateFormatter;

/**
 * A Class class.
 * <P>
 * @author HTHK
 */
public class ConcurBaseSqlCommand 
{
  private Statement stmt;
  private ResultSet rs;
  private PreparedStatement pstmt;
  private Connection con;

  public ConcurBaseSqlCommand(Connection con) 
  {
    this.con = con;
    stmt = null;
    pstmt = null;
    rs = null;
  }
  

  //changes:
  //set connection as false
  //
  public void update(ConcurrencyRow row) throws DataException, SQLException
  {
    try 
    {
      PrimaryKey pk = row.getPrimaryKey();
      UpdatableColumns uc = row.getUpdatableColumns();
      
      // ++++ ++++
      con.setAutoCommit(false);
      // ---- ----
      
      pstmt = con.prepareStatement(getUpdateSqlByPk(pk), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      
      if (pk == null || pk.getColumns() == null)
        throw new DataException("Primary key doesn't exist.");
      
      Column[] pkCols = pk.getColumns();
      for (int j = 0; j < pkCols.length; j++) 
      {
    	  //System.out.println(pkCols[j].getName()+"="+pkCols[j].getValue());
    	  setPreparedStatement(pstmt, j + 1, pkCols[j]);
      }
      
      rs = pstmt.executeQuery();
      Column[] columns = uc.getColumns();
      if (rs.absolute(1)) 
      {    	  
    	  //++++ ++++
    	  if(row.getTimeStamp().getValue() == null  || rs.getTimestamp(row.getTimeStampName()).equals(row.getTimeStamp().getValue()))
    	  {
          //---- ----
    		  
    		  // moves the cursor to the fifth row of rs; 
    		  // moves cursor to the insert row
    		  for (int i = 0; i < columns.length; i++) 
    		  {
    			  
    			  // ++++ ++++
    			  if(columns[i].getName().equals(row.getTimeStampName()))
    			  {
    				  Column tmp = columns[i].clone(columns[i]);
    				  tmp.setValue(DateFormatter.format_n(new java.sql.Timestamp((new java.util.Date()).getTime())));
    				  updateColumn(rs, tmp);
    			  }
    			  // ---- ----
    			  else if (columns[i] != null) 
    			  {
    				  System.out.println(i + "=" + columns[i].getName() + "=>" + columns[i].getValue());
    				  updateColumn(rs, columns[i]);
    			  }
    			  else 
    			  {
    				  System.out.println(i);
    			  }
    		  }
    		  rs.updateRow();
    	  }
    	  //++++ ++++
    	  else
    	  {
    		  throw new DataException("The record has been changed in database.");
    	  }
    	  //---- ----
      }
      else
      {
        throw new DataException("The record doesn't exist in database.");
      }
    }
    catch (DataException e) 
    {
      con.rollback();
      closeStatement();
      throw e;
    }
    catch (SQLException e) 
    {
      con.rollback();
      closeStatement();
      throw new DataException(e);
    }
    catch (Exception e) 
    {
      con.rollback();
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally 
    {
      closeStatement();
    }
  }

  public void delete(ConcurrencyRow row) throws DataException, SQLException
  {
    try 
    {
      PrimaryKey pk = row.getPrimaryKey();

      System.out.println(getSqlByPk(pk));

      pstmt = con.prepareStatement(getUpdateSqlByPk(pk), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      Column[] pkCols = pk.getColumns();
      
      for (int j = 0; j < pkCols.length; j++) 
      {
        setPreparedStatement(pstmt, j + 1, pkCols[j]);
        System.out.println(pkCols[j].getName()+"="+pkCols[j].getValue());
      }
      
      System.out.println("pk read done");
      rs = pstmt.executeQuery();

      if(rs == null)
      {
    	  System.out.println("rs null");
      }
      if (rs.absolute(1))
      { 
    	  //++++ ++++
    	  if(row.getTimeStamp() == null ||rs.getTimestamp(row.getTimeStampName()).equals(row.getTimeStamp().getValue()))
    	  {
          //---- ----
    		
    		  // moves the cursor to the fifth row of rs;
    		  // moves cursor to the insert row
    		  rs.deleteRow();
    	  
    	  //++++ ++++
    	  }
          else
          {
        	  throw new DataException("The record has been changed in database.");
          }
    	  //---- ----
      }
      else 
      {
        throw new DataException("error.sql.notExist");
      }
    }
    catch (DataException e) 
    {
      con.rollback();
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    catch (SQLException e) 
    {
      con.rollback();
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    catch (Exception e) 
    {
      con.rollback();
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally 
    {
      closeStatement();
    }
  }

  public void insert(Row row) throws DataException
  {
    try 
    {
      if (findByPk(row).size() > 0)
        throw new DataException("This record has already existed.");
    
      PrimaryKey pk = row.getPrimaryKey();
      stmt = con.createStatement
      (
    	  ResultSet.TYPE_SCROLL_INSENSITIVE,
          ResultSet.CONCUR_UPDATABLE);
          
      	  System.out.println(getNoRowsSql(pk));
          rs = stmt.executeQuery(getNoRowsSql(pk));

          Column[] columns = row.getColumns();
          rs.moveToInsertRow(); 
          
          // moves cursor to the insert row
          for (int i = 0; i < columns.length; i++)
          {
        	  if (columns[i].getInvalidInput()) 
        	  {
        		  System.out.println(columns[i].getName()+"="+columns[i].getValue());
        		  StringBuffer errorMapping = new StringBuffer("error.form.").append(pk.getTableName()).append(".").append(columns[i].getName());
        		  throw new DataException(errorMapping.toString());
        	  }
        	  updateColumn(rs, columns[i]);
          }
          
          rs.insertRow();
          rs.moveToCurrentRow();
      }
      catch (DataException e) 
      {
    	  closeStatement();
    	  e.printStackTrace();
    	  throw e;
      }
      catch (SQLException e) 
      {
    	  closeStatement();
    	  e.printStackTrace();
    	  throw new DataException(e);
      }
      catch (Exception e)
      {
    	  closeStatement();
    	  e.printStackTrace();
    	  throw new DataException(e);
      }
      finally
      {
    	  closeStatement();
      }
  }

  public Vector findByPk(Row row) throws DataException 
  {
    Vector rows = new Vector();
    try 
    {
      PrimaryKey pk = row.getPrimaryKey();
      pstmt = con.prepareStatement(getSqlByPk(pk), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      Column[] pkCols = pk.getColumns();
      for (int j = 0; j < pkCols.length; j++) 
      {
        setPreparedStatement(pstmt, j + 1, pkCols[j]);
      }
      
      rs = pstmt.executeQuery();
      for (int j = 0; rs.next(); j++) 
      {
        Column[] cols = row.createColumns();
        for (int i = 0; i < cols.length; i++) 
        {
          cols[i].setValue(getColumnValue(rs, cols[i]));
        }
        rows.add(j, cols);
      }
    }
    catch (DataException e) 
    {
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    catch (SQLException e) 
    {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    catch (Exception e) 
    {
      closeStatement();
      e.printStackTrace();
      throw new DataException(e);
    }
    finally
    {
      closeStatement();
    }
    
    return rows;
  }

  public Vector find(Row row) throws DataException 
  {
    Vector rows = new Vector();
    try
    {
      pstmt = con.prepareStatement(getSql(row));
      Column[] columns = row.getColumns();
      int k = 1;
      for (int j = 0; j < columns.length; j++) 
      {
        if (columns[j] != null && columns[j].getValue() != null) 
        {
          setPreparedStatement(pstmt, k, columns[j]);
          k++;
        }
      }
      rs = pstmt.executeQuery();
      for (int j = 0; rs.next(); j++)
      {
        Column[] cols = row.createColumns();
        for (int i = 0; i < cols.length; i++)
        {
          cols[i].setValue(getColumnValue(rs, cols[i]));
        }
        rows.add(j, cols);
      }
    }
    catch (DataException e) 
    {
      closeStatement();
      e.printStackTrace();
      throw e;
    }
    
    catch (SQLException e)
    {
      e.printStackTrace();
      
      closeStatement();
      throw new DataException(e);
    }
    catch (Exception e)
    {
      e.printStackTrace();
      closeStatement();
      throw new DataException(e);
    }
    finally 
    {
      closeStatement();
    }
    return rows;
  }

  private String getSql(Row row) 
  {
    String tableName = row.getPrimaryKey().getTableName();
    Column[] columns = row.getColumns();
    StringBuffer sql = new StringBuffer();
    sql = sql.append("SELECT ").append(tableName).append(".* FROM ").append(tableName);
    StringBuffer whereClause = null;
    
    for (int i = 0; i < columns.length; i++) 
    {
      if (columns[i] != null && columns[i].getValue() != null)
      {
        if (whereClause == null)
          whereClause = new StringBuffer(" where ");
        else
          whereClause = whereClause.append(" and ");
        
        if (columns[i].getType() == Column.Types.string)
          whereClause = whereClause.append(columns[i].getName()).append(" like ? ");
        else if (columns[i].getType() == Column.Types.integer)
          whereClause = whereClause.append(columns[i].getName()).append(" = ? ");
        else if (columns[i].getType() == Column.Types.number)
          whereClause = whereClause.append(columns[i].getName()).append(" = ? ");
        else if (columns[i].getType() == Column.Types.time)
          whereClause = whereClause.append("trunc(").append(columns[i].getName()).append(") = ? ");
        else if (columns[i].getType() == Column.Types.time_n)
          whereClause = whereClause.append("trunc(").append(columns[i].getName()).append(") = ? ");
      }
    }
    
    if (whereClause == null)
      whereClause = new StringBuffer();
    
      // append order
    PrimaryKey pk = row.getPrimaryKey();
    String[] keys = pk.getList();
    
    for (int i = 0; i < keys.length; i++) 
    {
      if (i == 0)
        whereClause = whereClause.append(" order by ").append(keys[i]);
      else
        whereClause = whereClause.append(", ").append(keys[i]);
    }
    
    sql = sql.append(whereClause.toString());
    return sql.toString();
    
  }

  private String getSqlByPk(PrimaryKey pk) throws DataException 
  {
    if (pk == null || pk.getColumns() == null || pk.getColumns().length == 0) 
    {
      throw new DataException("error.sql.pk");
    }
    
    StringBuffer sql = new StringBuffer();

    sql.append("SELECT ");
    sql.append(pk.getTableName());
    sql.append(".* FROM ");
    sql.append(pk.getTableName());
    sql.append(" where ");
    sql.append(pk.getColumns()[0].getName());
    sql.append(" = ? ");

    for (int i = 1; i < pk.getColumns().length; i++) 
    {
      sql.append(" and ").append(pk.getColumns()[i].getName()).append(" = ? ");
    }
    
    System.out.print(sql.toString());
    return sql.toString();
  }
  
  private String getUpdateSqlByPk(PrimaryKey pk) throws DataException 
  {
    if (pk == null || pk.getColumns() == null || pk.getColumns().length == 0) 
    {
      throw new DataException("error.sql.pk");
    }
    
    StringBuffer sql = new StringBuffer();

    sql.append("SELECT ");
    sql.append(pk.getTableName());
    sql.append(".* FROM ");
    sql.append(pk.getTableName());
    sql.append(" where ");
    sql.append(pk.getColumns()[0].getName());
    sql.append(" = ? ");


    for (int i = 1; i < pk.getColumns().length; i++) 
    {
      sql.append(" and ").append(pk.getColumns()[i].getName()).append(" = ? ");
    }
    
    //++++ ++++
    sql.append(" FOR UPDATE");
    //---- ----
    
    System.out.print(sql.toString());
    return sql.toString();
  }

  private String getNoRowsSql(PrimaryKey pk) throws DataException 
  {
    if (pk == null || pk.getColumns() == null || pk.getColumns().length == 0)
      throw new DataException("error.sql.pk");
    StringBuffer sql = new StringBuffer();
    sql = sql.append("SELECT ")
             .append(pk.getTableName())
    		 .append(".* FROM ")
    		 .append(pk.getTableName())
    		 .append(" where ")
    		 .append(pk.getColumns()[0].getName())
    		 .append(" is null ");
    
    return sql.toString();
  }

  private void setPreparedStatement(PreparedStatement pstmt, int j, Column column) throws DataException, SQLException 
  {
    if (column.getValue() != null)
      if (column.getType() == Column.Types.string)
        pstmt.setString(j, ( (String) column.getValue()).trim());
        //
      else if (column.getType() == Column.Types.integer)
        pstmt.setInt(j, ( (Integer) column.getValue()).intValue());
        //
      else if (column.getType() == Column.Types.number)
        pstmt.setBigDecimal(j, new BigDecimal(column.getValue().toString()));
      else if (column.getType() == Column.Types.time)
        pstmt.setTimestamp(j, (java.sql.Timestamp) column.getValue());
      else if (column.getType() == Column.Types.time_n)
        pstmt.setTimestamp(j, (java.sql.Timestamp) column.getValue());
      else
        throw new DataException("error.sql.invalidDataType");
  }

  private void updateColumn(ResultSet rs, Column column) throws DataException, SQLException 
  {
    /*Alex: if want update a null column and it is nullable ,
     so we can save null value back to DB. following is original source code
         if (columnA.getValue()!=null)
        if (columnA.getType()==Column.Types.string)
     rs.updateString(columnA.getName(),((String)columnA.getValue()).trim());
        else if (columnA.getType()==Column.Types.number)
          rs.updateDouble(columnA.getName(),((BigDecimal)columnA.getValue()).doubleValue());
        else if (columnA.getType()==Column.Types.time)
     rs.updateTimestamp(columnA.getName(),(java.sql.Timestamp)columnA.getValue());
        else
          throw new DataException("error.sql.invalidDataType");
     */
    System.out.println("column name=" + column.getName()+" & column value=" + column.getValue());
    if (column.getValue() == null) 
    {
      if (column.getNullable()) 
      {
        rs.updateNull(column.getName());
      }
    }
    else 
    {
      if (column.getType() == Column.Types.string)
        rs.updateString(column.getName(), ((String) column.getValue()).trim());
      else if (column.getType() == Column.Types.integer)
        rs.updateInt(column.getName(), ((Integer) column.getValue()).intValue());
      else if (column.getType() == Column.Types.number)
        rs.updateBigDecimal(column.getName(),(new BigDecimal(column.getValue().toString())));
      else if (column.getType() == Column.Types.time)
        rs.updateTimestamp(column.getName(),(java.sql.Timestamp) column.getValue());
      else if (column.getType() == Column.Types.time_n)
        rs.updateTimestamp(column.getName(),
                           (java.sql.Timestamp) column.getValue());
      else
        throw new DataException("error.sql.invalidDataType");
    }
  }

  private Serializable getColumnValue(ResultSet rs, Column column) throws DataException, SQLException 
  {

    if (column.getType() == Column.Types.string)
      return rs.getString(column.getName());
    else if (column.getType() == Column.Types.integer)
      return new Integer(rs.getInt(column.getName()));
    else if (column.getType() == Column.Types.number)
      return new BigDecimal(rs.getBigDecimal(column.getName()).doubleValue());
    else if (column.getType() == Column.Types.time)
      return rs.getTimestamp(column.getName());
    else if (column.getType() == Column.Types.time_n)
      return rs.getTimestamp(column.getName());
    else
      throw new DataException("error.sql.invalidDataType");
  }

  private void closeStatement() 
  {
    try 
    {
      if (rs != null)
        rs.close();
      if (stmt != null)
        stmt.close();
      if (pstmt != null)
        pstmt.close();
    }
    catch (Exception e) 
    {
    }
  }
}
